﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.ProviderBase;
using System.Text;

namespace System.Data.SugarClient
{
	

	public class SugarConnection  : DbConnection
	{

		#region Constructors

		public SugarConnection()
		{

		}

		public SugarConnection(
			string connectionString) : this()
		{
			this.ConnectionString = connectionString;
		}

		#endregion

		#region Fields

		private SugarSoapContext _soapContext;
		private readonly SugarConnectionOption _connectionOption = new SugarConnectionOption();

		#endregion

		#region Properties

		#region Overrides

		public override string DataSource
		{
			get { return this.ConnectionOption.DataSource; }
		}

		public override string ConnectionString
		{
			get { return this.ConnectionOption.ConnectionString; }
			set { this.ConnectionOption.ConnectionString = value; }
		}

		public override string ServerVersion
		{
			get { return this.GetSoapContextOrThrow().GetServerVersion(); }
		}

		public override ConnectionState State
		{
			get
			{
				if (string.IsNullOrEmpty(this.GetSoapContextOrThrow().Session)) return ConnectionState.Closed;
				else if ("05".Equals(this.GetSoapContextOrThrow().Session) || "10".Equals(this.GetSoapContextOrThrow().Session)) return ConnectionState.Broken;
				else return ConnectionState.Open;
			}
		}

		public override string Database
		{
			get { throw new NotImplementedException(); }
		}

		#endregion

		public int PageSize
		{
			get { return this.ConnectionOption.PageSize; }
			set { this.ConnectionOption.PageSize = value; }
		}

		internal SugarConnectionOption ConnectionOption
		{
			get { return _connectionOption; }
		}

		internal SugarSoapContext SoapContext
		{
			get { return this._soapContext; }
		}

		#endregion

		#region Methods

		#region Overrides

		public override void Open()
		{
			this._soapContext = new SugarSoapContext(this.ConnectionOption.DataSource);
			try
			{
				this._soapContext.Login(this.ConnectionOption.UserID, this.ConnectionOption.Password, this.ConnectionOption.EncryptionKey);
			}
			catch (SugarException ex)
			{
				Trace.Debug(ex.Message);
				Trace.Debug(ex.StackTrace);
				throw new SugarException(string.Format("Can not login to SugarCRM instance on {0}. {1}", this.DataSource, ex.Message), ex);
			}
		}

		public override void Close()
		{
			this.GetSoapContextOrThrow().Logout();
		}

		public override DataTable GetSchema()
		{
			return this.GetSchema(string.Empty);
		}

		public override DataTable GetSchema(string collectionName)
		{

			DataTable table = new DataTable();
			if (string.Empty.Equals(collectionName))
			{
				table.Columns.Add(new DataColumn("CollectionName", typeof(string)));
				table.Columns.Add(new DataColumn("NumberOfRestrictions", typeof(int)));
				table.Columns.Add(new DataColumn("NumberOfIdentifierParts", typeof(int)));

				DataRow row = table.NewRow();
				row["CollectionName"] = "Tables";
				row["NumberOfRestrictions"] = 0;
				row["NumberOfIdentifierParts"] = 0;
				table.Rows.Add(row);

				row = table.NewRow();
				row["CollectionName"] = "Columns";
				row["NumberOfRestrictions"] = 0;
				row["NumberOfIdentifierParts"] = 0;
				table.Rows.Add(row);
			}
			else if ("Tables".Equals(collectionName) || "Modules".Equals(collectionName) )
			{
				table.Columns.Add(new DataColumn("TABLE_CATALOG", typeof(string)));
				table.Columns.Add(new DataColumn("TABLE_SCHEMA", typeof(string)));
				table.Columns.Add(new DataColumn("TABLE_NAME", typeof(string)));
				table.Columns.Add(new DataColumn("TABLE_TYPE", typeof(string)));

				IGetAvailableModulesActionResult result = 
					this.SoapContext.ExecuteAction<IGetAvailableModulesActionResult>(SugarSoapActionType.GetAvailableModules, null);
				foreach (string module in result.AvailableModules)
				{
					DataRow row = table.NewRow();
					row["TABLE_CATALOG"] = this.DataSource;
					row["TABLE_SCHEMA"] = string.Empty;
					row["TABLE_NAME"] = module;
					row["TABLE_TYPE"] = "Module";
					table.Rows.Add(row);
				}
			}
			else if ("Columns".Equals(collectionName))
			{
				/*
					|TABLE_CATALOG               | System.String  |
					|TABLE_SCHEMA                | System.String  |
					|TABLE_NAME                  | System.String  |
					|COLUMN_NAME                 | System.String  |
					|ORDINAL_POSITION            | System.Int32   |
					|COLUMN_DEFAULT              | System.String  |
					|IS_NULLABLE                 | System.String  |
					|DATA_TYPE                   | System.String  |
					|CHARACTER_MAXIMUM_LENGTH    | System.Int32   |
					|CHARACTER_OCTET_LENGTH      | System.Int32   |
					|NUMERIC_PRECISION           | System.Byte    |
					|NUMERIC_PRECISION_RADIX     | System.Int16   |
					|NUMERIC_SCALE               | System.Int32   |
					|DATETIME_PRECISION          | System.Int16   |
					|CHARACTER_SET_CATALOG       | System.String  |
					|CHARACTER_SET_SCHEMA        | System.String  |
					|CHARACTER_SET_NAME          | System.String  |
					|COLLATION_CATALOG           | System.String  |
				 */
				table.Columns.Add(new DataColumn("TABLE_CATALOG", typeof(string)));
				table.Columns.Add(new DataColumn("TABLE_SCHEMA", typeof(string)));
				table.Columns.Add(new DataColumn("TABLE_NAME", typeof(string)));
				table.Columns.Add(new DataColumn("COLUMN_NAME", typeof(string)));
				table.Columns.Add(new DataColumn("ORDINAL_POSITION", typeof(int)));
				table.Columns.Add(new DataColumn("COLUMN_DEFAULT", typeof(string)));
				table.Columns.Add(new DataColumn("IS_NULLABLE", typeof(string)));
				table.Columns.Add(new DataColumn("DATA_TYPE", typeof(string)));

				foreach (DataRow row in this.GetSchema("Tables").Rows)
				{
					string module = row["TABLE_NAME"].ToString();
					try
					{
						IGetModuleFieldsActionResult result =
							this.SoapContext.ExecuteAction<IGetModuleFieldsActionResult>(
								SugarSoapActionType.GetModuleFields, module);

						for (int i = 0; i < result.VarDefs.Count; ++i)
						{
							SugarVarDef vardef = result.VarDefs[i];
							DataRow nrow = table.NewRow();
							nrow["TABLE_CATALOG"] = this.DataSource;
							nrow["TABLE_SCHEMA"] = string.Empty;
							nrow["TABLE_NAME"] = module;
							nrow["COLUMN_NAME"] = vardef.Name;
							nrow["ORDINAL_POSITION"] = i;
							nrow["COLUMN_DEFAULT"] = vardef.DefaultValue;
							nrow["IS_NULLABLE"] = !vardef.Required;
							nrow["DATA_TYPE"] = vardef.DataType.ToString();
							table.Rows.Add(nrow);
						}
					}
					catch (SugarException ex)
					{
						Trace.Debug("GetSchema(\"Columns\") failed at Table {0}", module);
					}
				}
			}
			return table;
		}

		public override DataTable GetSchema(string collectionName, string[] restrictionValues)
		{
			return base.GetSchema(collectionName, restrictionValues);
		}

		protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
		{
			throw new NotImplementedException();
		}

		public override void ChangeDatabase(string databaseName)
		{
			throw new NotImplementedException();
		}

		protected override DbCommand CreateDbCommand()
		{
			return new SugarCommand(this);
		}

		#endregion

		private SugarSoapContext GetSoapContextOrThrow()
		{
			if (this._soapContext == null)
			{
				throw new SugarException("Connection is closed.");
			}
			else 
			{
				return this._soapContext;
			}
		}

		#endregion

	}
}
